﻿
IF object_id(N'dbo.UDS_File_Directory', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.UDS_File_Directory
    (
        DirectoryId         int             NOT NULL IDENTITY(1,1),
        ParentDirectoryId   int             NOT NULL,
        OwnerId             int             NOT NULL,
        [Name]              nvarchar(256)   COLLATE Chinese_PRC_CI_AS NOT NULL,
        TimeCreated         datetime        NOT NULL CONSTRAINT [DF_UDS_File_Directory_TimeCreated] DEFAULT(getdate()),
        LastModified        datetime        NOT NULL CONSTRAINT [DF_UDS_File_Directory_LastModified] DEFAULT(getdate()),
        
        CONSTRAINT [PK_UDS_File_Directory] PRIMARY KEY NONCLUSTERED (DirectoryId asc),
        CONSTRAINT [FK_UDS_File_Directory_OwnerId] FOREIGN KEY (OwnerId) REFERENCES dbo.UDS_Staff(Staff_ID)
    );
    
    CREATE UNIQUE NONCLUSTERED INDEX [UX_UDS_File_Directory_Owner_Directory] ON dbo.UDS_File_Directory
    (
        DirectoryId,
        OwnerId
    );
    
    CREATE CLUSTERED INDEX [CX_UDS_File_Directory] ON dbo.UDS_File_Directory
    (
        OwnerId asc,
        [Name] asc
    );
    
    ALTER TABLE dbo.UDS_File_Directory ADD CONSTRAINT [FK_UDS_File_Directory_Parent] FOREIGN KEY (ParentDirectoryId, OwnerId) REFERENCES dbo.UDS_File_Directory(DirectoryId, OwnerId);
END
GO


-- Create a new CLASS
IF NOT EXISTS ( SELECT 1 FROM dbo.UDS_Class WHERE ClassID = 302 )
BEGIN
    SET IDENTITY_INSERT dbo.UDS_Class ON;
    
    INSERT INTO dbo.UDS_Class ( ClassID, ClassName, ClassRemark, ClassType, ClassParentID, ClassAddedBy, ClassAddedDate, [Status] )
    VALUES(302, N'网络存储', N'网络存储', 302, 302, N'admin', getdate(), 0);
    
    SET IDENTITY_INSERT dbo.UDS_Class OFF;
END
GO

-- Grant browse permission for all roles
INSERT INTO dbo.UDS_Assign_Rule (Act_ID, Based_On, Team_ID, Role_ID)
SELECT /* browse permission */ 1, /* role-based */3, 302, Role_ID
FROM dbo.UDS_Role
WHERE Role_ID NOT IN (
	SELECT Role_ID
	FROM dbo.UDS_Assign_Rule
	WHERE Act_ID = 1 AND Based_On = 3 AND Team_ID = 302
);
GO